//
//  database.cpp
//  magazzino
//
//  Created by Vito on 17/11/14.
//  Copyright (c) 2014 Vito. All rights reserved.
//

#include "database.h"
#include <iostream>
#include <sstream>
#include <string>
#include <stdlib.h>

Database::Database(const char* filename)
{
    database = NULL;
    open(filename);
}

Database::~Database()
{
}

bool Database::open(const char* filename)
{
    if(sqlite3_open(filename, &database) == SQLITE_OK)
        return true;
    
    return false;
}

bool Database::createItemsTable()
{
    char* create="CREATE TABLE items (code VARCHAR(100), description VARCHAR(100), quantity DOUBLE, price DOUBLE);";
    query(create);
    return true;
}

void Database::cleanItemsTable(){
    char* deleteAll="DELETE FROM items;";
    query(deleteAll);
}
bool Database::insertItems(vector<Item> items){
    
    int n = 0;
    for (n=0; n<items.size(); n++) {
        
        stringstream sstm;
        if (!exist(items[n].code)){
            sstm << "INSERT INTO items VALUES('" << items[n].code << "','"<<items[n].description<<"',"<< items[n].qnt<<","<<items[n].price<<");";
        } else {
            sstm << "UPDATE ITEMS set quantity=quantity+"<<items[n].qnt<<" WHERE code='" <<items[n].code<<"';";
            
        }
        query(&sstm.str()[0]);
        
    }
    
    return true;
}


vector<Item> Database::loadItems(){
    vector<Item> items;
    
    vector<vector<string> > result = query("SELECT * FROM items;");
    if (result.size()>0){
        for(vector<vector<string> >::iterator it = result.begin(); it < result.end(); ++it)
        {
            Item item;
            vector<string> row = *it;
            item.code = row.at(0);
            item.description = row.at(1);
            item.qnt = atof(&row.at(2)[0]) ;
            item.price = atof(&row.at(3)[0]) ;
            items.push_back(item);
        }
    }
    return items;
}

Item Database::loadItem(string code){
    Item item;
    item.code = "0";
    stringstream sstm;
    sstm << "SELECT * FROM items WHERE CODE = '" << code << "';";
    vector<vector<string> > result = query(&sstm.str()[0]);
    if (result.size()>0){
        item.code = result.at(0).at(0);
        item.description = result.at(0).at(1);
        item.qnt = atof(&result.at(0).at(2)[0]) ;
        item.price = atof(&result.at(0).at(3)[0]) ;
        
    }
    return item;
}


bool Database::exist(string code){
    stringstream sstm;
    sstm << "SELECT * FROM items WHERE CODE = '" << code << "';";
    vector<vector<string> > result = query(&sstm.str()[0]);
    if (result.size()>0){
        return true;
    }
    return false;
}

vector<vector<string> > Database::query(char* queryC)
{
    sqlite3_stmt *statement;
    vector<vector<string> > results;
    
    if(sqlite3_prepare_v2(database, queryC, -1, &statement, 0) == SQLITE_OK)
    {
        int cols = sqlite3_column_count(statement);
        int result = 0;
        while(true)
        {
            result = sqlite3_step(statement);
            
            if(result == SQLITE_ROW)
            {
                vector<string> values;
                for(int col = 0; col < cols; col++)
                {
                    values.push_back((char*)sqlite3_column_text(statement, col));
                }
                results.push_back(values);
            }
            else
            {
                break;
            }
        }
        
        sqlite3_finalize(statement);
    }
    
    string error = sqlite3_errmsg(database);
    if(error != "not an error") cout << queryC << " " << error << endl;
    
    return results;
}



void Database::close()
{
    sqlite3_close(database);
}


